﻿using System;
using System.Data;
using System.Text;
using System.Collections.Generic;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.Common;

namespace Chenxin.Lib.House.DAL
{
    /// <summary>
    /// 数据访问类House_DataCollection。
    /// </summary>
    public class House_DataCollection
    {
        public House_DataCollection()
        { }
        #region  成员方法

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public static DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select RecordId,UserId,UserCode,UserName,CityId,CityName,CompanyId,CompanyName,AreaId,AreaName,DepartId,DepartName,ValidCount_Sale,ValidCount_Rent,RespiteCount_Sale,RespiteCount_Rent,HouseTraceCount_Sale,HouseTraceCount_Rent,HouseTraceCount_SaleRent,CustomerTraceCount_Rent,CustomerTraceCount_Sale,SystemHouseTraceCount,SystemCustomerTraceCount,KeyCount,MarkedCount,ViewCount,HouseCustomerCount,DemandCustomerCount,BuyCount_Flat,BuyCount_Business,BuyCount_Office,RentCount_Flat,RentCount_Business,RentCount_Office,ViewPhoneCount,AddTime ");
            strSql.Append(" FROM House_DataCollection ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            Database db = DatabaseFactory.CreateDatabase("Chenxin_House_Search");
            return db.ExecuteDataSet(CommandType.Text, strSql.ToString());
        }


        public static DataSet GetArchivementCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
        
            strSql.Append("(Sum(CASE WHEN IsOpen = 1 AND IsCalculate = 1 AND DepartKind <> '总部' THEN ValidCount_Sale ELSE 0 END) +");
            strSql.Append("Sum(CASE WHEN IsOpen = 1 AND IsCalculate = 1 AND DepartKind <> '总部' THEN ValidCount_Rent ELSE 0 END) +");
            strSql.Append("Sum(CASE WHEN IsOpen = 1 AND IsCalculate = 1 AND DepartKind <> '总部' THEN RespiteCount_Sale ELSE 0 END) +");
            strSql.Append("Sum(CASE WHEN IsOpen = 1 AND IsCalculate = 1 AND DepartKind <> '总部' THEN RespiteCount_Rent ELSE 0 END)) As HouseCount,");


            strSql.Append("(Sum(CASE WHEN IsOpen = 1 AND IsCalculate = 1 AND DepartKind <> '总部' THEN HouseTraceCount_Sale ELSE 0 END) +");
            strSql.Append("Sum(CASE WHEN IsOpen = 1 AND IsCalculate = 1 AND DepartKind <> '总部' THEN HouseTraceCount_Rent ELSE 0 END) +");
            strSql.Append("Sum(CASE WHEN IsOpen = 1 AND IsCalculate = 1 AND DepartKind <> '总部' THEN HouseTraceCount_SaleRent ELSE 0 END) +");
            strSql.Append("Sum(CASE WHEN IsOpen = 1 AND IsCalculate = 1 AND DepartKind <> '总部' THEN SystemHouseTraceCount ELSE 0 END)) As HouseTraceCount,");


            strSql.Append("(Sum(CASE WHEN IsOpen = 1 AND IsCalculate = 1 AND DepartKind <> '总部' THEN BuyCount_Flat ELSE 0 END) +");
            strSql.Append("Sum(CASE WHEN IsOpen = 1 AND IsCalculate = 1 AND DepartKind <> '总部' THEN BuyCount_Business ELSE 0 END) +");
            strSql.Append("Sum(CASE WHEN IsOpen = 1 AND IsCalculate = 1 AND DepartKind <> '总部' THEN BuyCount_Office ELSE 0 END) +");
            strSql.Append("Sum(CASE WHEN IsOpen = 1 AND IsCalculate = 1 AND DepartKind <> '总部' THEN RentCount_Flat ELSE 0 END) +");
            strSql.Append("Sum(CASE WHEN IsOpen = 1 AND IsCalculate = 1 AND DepartKind <> '总部' THEN RentCount_Business ELSE 0 END) +");
            strSql.Append("Sum(CASE WHEN IsOpen = 1 AND IsCalculate = 1 AND DepartKind <> '总部' THEN RentCount_Office ELSE 0 END)) As CustomerCount,");


            strSql.Append("(Sum(CASE WHEN IsOpen = 1 AND IsCalculate = 1 AND DepartKind <> '总部' THEN CustomerTraceCount_Rent ELSE 0 END) +");
            strSql.Append("Sum(CASE WHEN IsOpen = 1 AND IsCalculate = 1 AND DepartKind <> '总部' THEN CustomerTraceCount_Sale ELSE 0 END) +");
            strSql.Append("Sum(CASE WHEN IsOpen = 1 AND IsCalculate = 1 AND DepartKind <> '总部' THEN SystemCustomerTraceCount ELSE 0 END)) As CustomerTraceCount,");

            strSql.Append("Sum(CASE WHEN IsOpen = 1 AND IsCalculate = 1 AND DepartKind <> '总部' THEN ViewCount ELSE 0 END) As ViewCount ");

            strSql.Append("from House_DataCollection left join v_Hr_DepartmentInfo");
            strSql.Append(" ON (House_DataCollection.DepartId = v_Hr_DepartmentInfo.DepartId)");

            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            Database db = DatabaseFactory.CreateDatabase("Chenxin_House_Search");
            return db.ExecuteDataSet(CommandType.Text, strSql.ToString());
        }

        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public static DataSet GetList(int PageIndex, int PageSize, String OrderField, string TableName, string StrWhere,string GroupList, string FieldList, string TableFieldList, bool DoCount)
        {
            Database db = DatabaseFactory.CreateDatabase("Chenxin_House_Search");
            DbCommand dbCommand = db.GetStoredProcCommand("PagerMultiTableGroup");
            db.AddInParameter(dbCommand, "PageIndex", DbType.Int32, PageIndex);
            db.AddInParameter(dbCommand, "PageSize", DbType.Int32, PageSize);
            db.AddInParameter(dbCommand, "OrderField", DbType.AnsiString, OrderField);
            db.AddInParameter(dbCommand, "TableName", DbType.AnsiString, TableName);
            db.AddInParameter(dbCommand, "StrWhere", DbType.AnsiString, StrWhere);
            db.AddInParameter(dbCommand, "GroupList", DbType.AnsiString, GroupList);
            db.AddInParameter(dbCommand, "FieldList", DbType.AnsiString, FieldList);
            db.AddInParameter(dbCommand, "TableFieldList", DbType.AnsiString, TableFieldList);
            db.AddInParameter(dbCommand, "DoCount", DbType.Boolean, DoCount);
            return db.ExecuteDataSet(dbCommand);
        }

        /// <summary>
        /// 获得数据列表（比DataSet效率高，推荐使用）
        /// </summary>
        public static List<Chenxin.Lib.House.Entity.House_DataCollection> GetListArray(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select RecordId,UserId,UserCode,UserName,CityId,CityName,CompanyId,CompanyName,AreaId,AreaName,DepartId,DepartName,ValidCount_Sale,ValidCount_Rent,RespiteCount_Sale,RespiteCount_Rent,HouseTraceCount_Sale,HouseTraceCount_Rent,HouseTraceCount_SaleRent,CustomerTraceCount_Rent,CustomerTraceCount_Sale,SystemHouseTraceCount,SystemCustomerTraceCount,KeyCount,MarkedCount,ViewCount,HouseCustomerCount,DemandCustomerCount,BuyCount_Flat,BuyCount_Business,BuyCount_Office,RentCount_Flat,RentCount_Business,RentCount_Office,ViewPhoneCount,AddTime ");
            strSql.Append(" FROM House_DataCollection ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            List<Chenxin.Lib.House.Entity.House_DataCollection> list = new List<Chenxin.Lib.House.Entity.House_DataCollection>();
            Database db = DatabaseFactory.CreateDatabase("Chenxin_House_Search");
            using (IDataReader dataReader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (dataReader.Read())
                {
                    list.Add(ReaderBind(dataReader));
                }
            }
            return list;
        }


        /// <summary>
        /// 对象实体绑定数据
        /// </summary>
        public static Chenxin.Lib.House.Entity.House_DataCollection ReaderBind(IDataReader dataReader)
        {
            Chenxin.Lib.House.Entity.House_DataCollection model = new Chenxin.Lib.House.Entity.House_DataCollection();
            object ojb;
            ojb = dataReader["RecordId"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.RecordId = new Guid(ojb.ToString());
            }
            ojb = dataReader["UserId"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.UserId = new Guid(ojb.ToString());
            }
            model.UserCode = dataReader["UserCode"].ToString();
            model.UserName = dataReader["UserName"].ToString();
            ojb = dataReader["CityId"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.CityId = new Guid(ojb.ToString());
            }
            model.CityName = dataReader["CityName"].ToString();
            ojb = dataReader["CompanyId"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.CompanyId = new Guid(ojb.ToString());
            }
            model.CompanyName = dataReader["CompanyName"].ToString();
            ojb = dataReader["AreaId"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.AreaId = new Guid(ojb.ToString());
            }
            model.AreaName = dataReader["AreaName"].ToString();
            ojb = dataReader["DepartId"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.DepartId = new Guid(ojb.ToString());
            }
            model.DepartName = dataReader["DepartName"].ToString();
            ojb = dataReader["ValidCount_Sale"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.ValidCount_Sale = (int)ojb;
            }
            ojb = dataReader["ValidCount_Rent"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.ValidCount_Rent = (int)ojb;
            }
            ojb = dataReader["RespiteCount_Sale"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.RespiteCount_Sale = (int)ojb;
            }
            ojb = dataReader["RespiteCount_Rent"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.RespiteCount_Rent = (int)ojb;
            }
            ojb = dataReader["HouseTraceCount_Sale"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.HouseTraceCount_Sale = (int)ojb;
            }
            ojb = dataReader["HouseTraceCount_Rent"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.HouseTraceCount_Rent = (int)ojb;
            }
            ojb = dataReader["HouseTraceCount_SaleRent"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.HouseTraceCount_SaleRent = (int)ojb;
            }
            ojb = dataReader["CustomerTraceCount_Rent"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.CustomerTraceCount_Rent = (int)ojb;
            }
            ojb = dataReader["CustomerTraceCount_Sale"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.CustomerTraceCount_Sale = (int)ojb;
            }
            ojb = dataReader["SystemHouseTraceCount"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.SystemHouseTraceCount = (int)ojb;
            }
            ojb = dataReader["SystemCustomerTraceCount"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.SystemCustomerTraceCount = (int)ojb;
            }
            ojb = dataReader["KeyCount"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.KeyCount = (int)ojb;
            }
            ojb = dataReader["MarkedCount"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.MarkedCount = (int)ojb;
            }
            ojb = dataReader["ViewCount"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.ViewCount = (int)ojb;
            }
            ojb = dataReader["HouseCustomerCount"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.HouseCustomerCount = (int)ojb;
            }
            ojb = dataReader["DemandCustomerCount"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.DemandCustomerCount = (int)ojb;
            }
            ojb = dataReader["BuyCount_Flat"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.BuyCount_Flat = (int)ojb;
            }
            ojb = dataReader["BuyCount_Business"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.BuyCount_Business = (int)ojb;
            }
            ojb = dataReader["BuyCount_Office"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.BuyCount_Office = (int)ojb;
            }
            ojb = dataReader["RentCount_Flat"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.RentCount_Flat = (int)ojb;
            }
            ojb = dataReader["RentCount_Business"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.RentCount_Business = (int)ojb;
            }
            ojb = dataReader["RentCount_Office"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.RentCount_Office = (int)ojb;
            }
            ojb = dataReader["ViewPhoneCount"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.ViewPhoneCount = (int)ojb;
            }
            ojb = dataReader["AddTime"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.AddTime = (DateTime)ojb;
            }
            return model;
        }

        /// <summary>
        /// 营业数据汇总
        /// </summary>
        /// <param name="NodeType">集合类型(城市,公司,商圈,部门,人员)</param>
        /// <param name="ObjectId">集合节点ID</param>
        /// <returns>集合数据集</returns>
        public static DataSet GetCollection(int NodeType,Guid ObjectId,DateTime StartDate,DateTime EndDate)
        {
            Database db = DatabaseFactory.CreateDatabase("Chenxin_House_Search");
            DbCommand dbCommand = db.GetStoredProcCommand("P_CollectBusinessData");
            db.AddInParameter(dbCommand, "NodeType", DbType.Int32, NodeType);
            db.AddInParameter(dbCommand, "ObjectId", DbType.Guid, ObjectId);
            db.AddInParameter(dbCommand, "StartDate", DbType.DateTime, StartDate);
            db.AddInParameter(dbCommand, "EndDate", DbType.DateTime, EndDate);
            return db.ExecuteDataSet(dbCommand);
        }
        #endregion  成员方法
    }
}

